﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data.SqlClient;
using System.IO;
using System.Data;

namespace MySkiPatrolDAL
{
    /// <summary>
    /// 
    /// SqlUtility provides common SQL methods to access SQL Server.
    /// 
    /// </summary>
    public class SqlConnector
    {
        //private logger myLog = new logger("sqlUtility.log");
        private static SqlConnector _instance;
        //private const string DB_CONFIG_FILE = System.Configuration.ConfigurationManager.ConnectionStrings(System.Environment.GetEnvironmentVariable("MIGRATIONLEVEL").Substring(1,2)).Connection;
        private static string _connectionStr = "";
        private const string CONNECTION_STR_NOT_FOUND_MSG = "Unable to get the connection string from the database config file.  ";
        ArrayList privateParamList = new ArrayList();
        private SqlConnection _connection;
        private string LogFile = string.Empty;

        public void writeLog(string message)
        {
            DateTime dt = DateTime.Now;
            string dttm = dt.ToShortDateString() + " - " + dt.ToShortTimeString();

            StreamWriter strmWrt = new StreamWriter(LogFile, true);
            strmWrt.WriteLine("[" + dttm + "] " + message + "/n");
            strmWrt.Close();

        }

        public void writeError(Exception ex)
        {
            writeLog(ex.Message + " || " + ex.StackTrace);
        }


        public void Dispose()
        {
            if (_instance != null)
            {
                _instance = null;
                //myLog.Dispose();
                //myLog = null;
            }
        }

        public SqlConnector()
        {
            // Get the database connection string parameters
            try
            {
                //TODO: LOGFILE?
                //LogFile = System.Web.HttpContext.Current.Application["LogPath"] + "//SQL.LOG";

                try
                {
                    _connectionStr = System.Configuration.ConfigurationManager.ConnectionStrings["SQL"].ConnectionString;
                }
                catch (Exception ex)
                {
                    //myLog.Trace(DateTime.Now + " | " + ex.ToString());
                    throw ex;
                }
            }
            catch (Exception exp)
            {
                //myLog.Trace(DateTime.Now + " | " + exp.ToString());
                throw new Exception(exp.ToString());
            }
        }


        /// <summary>
        /// Creates a single instance of SqlUtility.  Will read the 'db.config' file to
        /// get the connection string.  
        /// </summary>
        /// <returns></returns>
        public static SqlConnector Instance()
        {
            if (_instance == null)
            {
                lock (typeof(SqlConnector))
                {
                    if (_instance == null)
                    {
                        _instance = new SqlConnector();
                    }
                }
            }
            return _instance;
        }

        /// <summary>
        /// Creates a SQL connection using the input connection string
        /// </summary>
        /// <param name="connectionStr"></param>
        /// <returns></returns>
        public SqlConnection getConnection(string connectionStr)
        {
            SqlConnection conn = null;

            try
            {
                conn = openConnection(connectionStr);
                _instance = this;
                _connection = conn;
            }
            catch (Exception exp)
            {
                //myLog.Trace(DateTime.Now + " | " + exp.ToString());
                throw new Exception(exp.ToString());
            }

            return conn;
        }


        /// <summary>
        /// Creates a SQL connection using connection information provided
        /// in a standard db.config file
        /// </summary>
        /// <returns></returns>
        public SqlConnection getConnection()
        {
            SqlConnection conn = null;

            if (_connection == null)
            {
                // If not a valid connection string, throw an error
                if (_connectionStr == null)
                {
                    throw new Exception(CONNECTION_STR_NOT_FOUND_MSG + this.ToString());
                }
                else
                {
                    try
                    {
                        conn = openConnection(_connectionStr);
                        _instance = this;
                        _connection = conn;
                    }
                    catch (Exception exp)
                    {
                        //myLog.Trace(DateTime.Now + " | " + exp.ToString());
                        throw new Exception(exp.ToString());
                    }
                }
            }
            else
            {
                conn = _connection;
            }
            return conn;
        }


        private SqlConnection openConnection(string connectionStr)
        {
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection(connectionStr);
                conn.Open();
                _instance = this;
                _connection = conn;
            }
            catch (Exception exp)
            {
                //myLog.Trace(DateTime.Now + " | " + exp.ToString());
                throw new Exception(connectionStr + " " + exp.ToString());
            }
            return conn;
        }

        /// <summary>
        /// Close database connection.
        /// </summary>
        public void closeConnection(SqlConnection conn)
        {
            try
            {
                conn.Close();
                conn.Dispose();
            }
            catch (Exception exp)
            {
                //myLog.Trace(DateTime.Now + " | " + exp.ToString());
                //throw new Exception(exp.ToString());
            }
        }

        public DataSet RunSP_DataSet(string spName, string tableName)
        {
            DataSet ds = new DataSet();
            try
            {
                //Setup the SQL command and connection
                SqlCommand cmd = new SqlCommand();
                if (_instance == null)
                {
                    cmd.Connection = getConnection();
                }
                else
                {
                    cmd.Connection = _connection;
                }

                //Set the command SP and parameters
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = spName;

                //process any parameters in the arraylist
                if (privateParamList.Count > 0)
                {
                    foreach (dbParameter param in privateParamList)
                    {
                        SqlParameter addParam = null;
                        addParam = cmd.Parameters.Add(param.ParameterName, param.ParameterType);
                        addParam.Value = param.ParameterValue;
                    }
                }

                //get the data in a datatable
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds, tableName);
                //DataTable dt = ds.Tables["Account"];
            }
            catch (Exception exp)
            {
                //myLog.Trace(DateTime.Now + " | " + exp.ToString());
            }
            return ds;
        }

        public DataSet RunSQL_DataSet(string sqlText, string tableName)
        {
            DataSet ds = new DataSet();
            try
            {
                //Setup the SQL command and connection
                SqlCommand cmd = new SqlCommand();
                if (_connection == null)
                {
                    cmd.Connection = getConnection();
                }
                else
                {
                    cmd.Connection = _connection;

                    if (cmd.Connection.State == ConnectionState.Closed)
                    {
                        cmd.Connection.ConnectionString = _connectionStr;
                        cmd.Connection.Open();
                    }
                }

                //Set the command SP and parameters
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sqlText;

                //get the data in a datatable
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds, tableName);
                //DataTable dt = ds.Tables["Account"];

            }
            catch (Exception exp)
            {
                throw exp;
                //myLog.Trace(DateTime.Now + " | " + exp.ToString());
            }
            return ds;
        }

        public void AddParameter(string pName, SqlDbType pType, object pValue)
        {
            dbParameter myParam = new dbParameter(pName, pType, pValue);
            privateParamList.Add(myParam);
        }

        public void ClearParameters()
        {
            try
            {
                privateParamList.Clear();
            }
            catch (Exception ex)
            {
                //myLog.Trace(DateTime.Now + " | " + ex.ToString());
                throw new Exception("Parameter list did not clear.", ex);
            }
        }
    }

    public partial class dbParameter
    {
        public string ParameterName;

        public SqlDbType ParameterType;

        public Object ParameterValue;

        public dbParameter(string pName, SqlDbType pType, object pValue)
        {
            ParameterName = pName;
            ParameterType = pType;
            ParameterValue = pValue;
        }
    }
}
